package com.wirelesshomecontrol.db;

import java.sql.*;

import com.vaadin.data.util.IndexedContainer;
import com.vaadin.data.util.QueryContainer;
import com.vaadin.ui.*;
import com.wirelesshomecontrol.gui.windows.HistoryWindow;

public class jdbcconnect {

	String dbtime;
	public String dbUrl = "jdbc:mysql://localhost:3306/homemonitoringdb";
	String dbClass = "com.mysql.jdbc.Driver";
	String[] userArray = new String[2];
	
	public QueryContainer qcSQLHistory = null;
	public QueryContainer qcSQLSensor = null;
	String query = "Select username,password FROM Users";
	private HistoryWindow historywindow;
	public QueryContainer qcSQLAddUser;
	public QueryContainer qcSQLDelUser;
	public IndexedContainer ICdata;
	private static final String log_insert = "INSERT INTO log" + " VALUES(?,?,?,?,?)";
	
	public void connect(){
		try {
 
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl,"root","Hippos44");
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while (rs.next()) {
			dbtime = rs.getString(1);
			System.out.println(dbtime);
			} //end while

			con.close();
			} //end try

			catch(ClassNotFoundException e) {
			e.printStackTrace();
			}

			catch(SQLException e) {
			e.printStackTrace();
			}
	}
	
	public void Update_Log_DB(String historyType, int sensor_id, Timestamp datetime, String msg) {
		query = "INSERT INTO log " + "VALUES(0, ?, ?, ?, ? )";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl,"root","Hippos44");
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setString(1, historyType);
			stmt.setInt(2, sensor_id);
			stmt.setTimestamp(3, datetime);
			stmt.setString(4, msg);
			int rs = stmt.executeUpdate();

			con.close();
			} //end try

			catch(ClassNotFoundException e) {
			e.printStackTrace();
			}

			catch(SQLException e) {
			e.printStackTrace();
			}
	}
	
	public void Update_Sensor_Data_DB(String sensor_address, String nickname, String status, Timestamp datetime) {
		query = "INSERT INTO sensor_history " + "VALUES(0, ?, ?, ?, ? )";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl,"root","Hippos44");
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setString(1, sensor_address);
			stmt.setString(2, nickname);
			stmt.setString(3, status);
			stmt.setTimestamp(4, datetime);
			int rs = stmt.executeUpdate();

			con.close();
			} //end try

			catch(ClassNotFoundException e) {
			e.printStackTrace();
			}

			catch(SQLException e) {
			e.printStackTrace();
			}
	}
	
	public void Update_Status_DB(int idsensor, int status) {
		System.out.println(status);
		System.out.println(idsensor);
		query = "UPDATE sensors SET status=" + status + " WHERE idsensors=" + "'"+ idsensor +"'";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl,"root","Hippos44");
			PreparedStatement stmt = con.prepareStatement(query);
			int rs = stmt.executeUpdate();

			con.close();
			} //end try

			catch(ClassNotFoundException e) {
			e.printStackTrace();
			}

			catch(SQLException e) {
			e.printStackTrace();
			}
	}
	public void Update_User_DB(Object username, Object password) {
		
		query = "INSERT INTO user_login " + "VALUES(0, ?, ?)";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection (dbUrl,"root","Hippos44");
			PreparedStatement stmt = con.prepareStatement(query);
			stmt.setString(1, (String) username);
			stmt.setString(2, (String) password);
			int rs = stmt.executeUpdate();

			con.close();
			} //end try

			catch(ClassNotFoundException e) {
			e.printStackTrace();
			}

			catch(SQLException e) {
			e.printStackTrace();
			}
	}
	
	public void Delete_User_DB(Object username) {
		
	}
	public void Clr_SenorData_Table(){ 
	try {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection(dbUrl, "root","Hippos44");
		qcSQLHistory = new QueryContainer("TRUNCATE TABLE sensor_data", con);
		
		// cn.qcSQLHistory.close(); // Closes Query Can Not reopen

	} catch (SQLException e) {
		e.printStackTrace();
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	}catch (Exception e) {
		e.printStackTrace();
	}
	}
	public void Clr_Log_Table(){ 
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root","Hippos44");
			qcSQLHistory = new QueryContainer("TRUNCATE TABLE log", con);
			
			// cn.qcSQLHistory.close(); // Closes Query Can Not reopen

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		}
	public void View_DB_History() {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root","Hippos44");
			qcSQLHistory = new QueryContainer("SELECT * FROM log", con);
			//tblDataTable = new Table();

			historywindow.tblDataTable.setContainerDataSource(qcSQLHistory);

			historywindow.tblDataTable.setVisible(true);
			historywindow.tblDataTable.setEnabled(true);
			historywindow.tblDataTable.setCaption("History Information");
			//historywindow.addComponent(tblDataTable);
			// cn.qcSQLHistory.close(); // Closes Query Can Not reopen

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection(dbUrl, "root","Hippos44");
			qcSQLSensor = new QueryContainer("SELECT * FROM sensor_data", con);
			//tblDataTable2 = new Table();

			historywindow.tblDataTable2.setContainerDataSource(qcSQLSensor);

			historywindow.tblDataTable2.setVisible(true);
			historywindow.tblDataTable2.setEnabled(true);
			historywindow.tblDataTable2.setCaption("Sensor Information");
			//historywindow.historyWindow.addComponent(tblDataTable2);
			// cn.qcSQLHistory.close(); // Closes Query Can Not reopen
				//con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
	}
	}


